﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace DataBaseConnection
{
    /// <summary>
    /// 微软本地小型数据库 ACCESS
    /// </summary>
    public class CMSAccess:PConnOrder
    {
        public CMSAccess()
        { 
        
        }

        public CMSAccess(string _ConnectionString)
            : base(_ConnectionString)
        {
            
        }        

        #region 需要重写的方法               
        
        public override DataBaseType CurrentBaseType
        {
            get { return DataBaseType.ACCESS; }
        }

        public override DataSet ExecuteDataSet(string sqlText, IDataParameter[] cmdParms, CommandType comType)
        {                        
            //对象处理
            PrepareCommand( sqlText, cmdParms, comType);

            try
            {
                //创建OleDbDataAdapter对象以及DataSet
                OleDbDataAdapter da = new OleDbDataAdapter((OleDbCommand)cmd);
                DataSet ds = new DataSet();
                //填充ds
                da.Fill(ds);
                //返回ds
                return ds;
            }
            catch (System.Exception)
            {
                throw;
            }            
        }
        public override int ExecuteNonQuery(string sqlText, IDataParameter[] cmdParms, CommandType comType)
        {           
            //对象处理
            PrepareCommand( sqlText, cmdParms, comType);
            int result = 0;	//要输出的对象

            try
            {
                result = cmd.ExecuteNonQuery();
            }
            catch (System.Exception)
            {
                throw;
            }            

            return result;        

        }
        public override IDataReader ExecuteReader(string sqlText, IDataParameter[] cmdParms, CommandType comType)
        {
            //对象处理
            PrepareCommand( sqlText, cmdParms, comType);
            try
            {                
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (System.Exception)
            {
                throw;
            }            

        }
        public override object ExecuteScalar(string sqlText, IDataParameter[] cmdParms, CommandType comType)
        {
            //对象处理
            PrepareCommand( sqlText, cmdParms, comType);
            object result = null;	//要输出的对象

            try
            {
                result = (object)cmd.ExecuteScalar();	//返回查询结果的第一行第一列
            }
            catch (System.Exception)
            {
                throw;
            }            

            return result;

        }        
        public override void TransactionStart()
        {
            if (conn == null)
            {
                conn = new OleDbConnection();
                conn.ConnectionString = ConnectionString;
                conn.Open();
            }
            if (cmd == null)
            {
                cmd = new OleDbCommand();
            }
            
            myTrans = ((OleDbConnection)conn).BeginTransaction();
            cmd.Transaction = myTrans;
            
        }
        public override void TransactionEnd()
        {
            try
            {
                myTrans.Commit();
            }
            catch (Exception ex)
            {
                myTrans.Rollback();
                throw new Exception("操作失败，成功回滚!错误信息：" + ex.Message);
            }
        }
        
        public override IDataParameter GetNewParameter(string parameterName, object Value)
        {
            Type cType = Value.GetType();
            if (cType == typeof(DateTime))
            {
                Value = Convert.ToDateTime(((DateTime)Value).ToString("yyyy-MM-dd H:mm:ss"));
            }

            OleDbParameter par = new OleDbParameter(parameterName, Value);
            
            return par;
        }

        public override IDataParameter GetNewParameter(string parameterName, object Value, ParameterDirection OutType)
        {
            OleDbParameter par = new OleDbParameter(parameterName, Value);
            par.Direction = OutType;
            return par;
        }

        /// <summary>
        /// 集中处理连接属性
        /// </summary>        
        protected override void PrepareCommand(string cmdText, IDataParameter[] cmdParms, CommandType ct)
        {
            if (conn == null) { conn = new OleDbConnection(); }
            if (cmd == null) { cmd = new OleDbCommand(); }
            base.PrepareCommand(cmdText, cmdParms, ct);
        }

        public override DataSet SplitPage(string sqlText, string Primary, string Orderby, int MaxRows, int CurrPage)
        {
            //索引过滤
            if (CurrPage < 1) { CurrPage = 1; }
            CurrPage--;

            if (Orderby != null && Orderby != string.Empty)
            {
                sqlText += " ORDER BY " + Orderby;
            }

            DataSet ds = new DataSet();
            DataTable SourceDT = new DataTable();
            IDataReader idr = ExecuteReader(sqlText, null, CommandType.Text);
            //获取匹配的表结构
            for (int dc = 0; dc < idr.FieldCount; dc++)
            {
                SourceDT.Columns.Add(idr.GetName(dc));
            }
            int num = 0;
            int startIndex = MaxRows * CurrPage;
            int endIndex = startIndex + MaxRows;

            while (idr.Read())
            {
                //判断截取数据范围                
                if (num >= startIndex && num < endIndex)
                {
                    //将每行的数据转移至DataTable
                    DataRow currdr = SourceDT.NewRow();
                    for (int i = 0; i < SourceDT.Columns.Count; i++)
                    {
                        currdr[i] = idr.GetValue(i);
                    }
                    SourceDT.Rows.Add(currdr);
                }

                num++;
            }

            idr.Close();//关闭

            //加载数据            
            ds.Tables.Add(SourceDT);
            //总行数
            DataTable countDT = new DataTable();
            countDT.Columns.Add("count");
            DataRow cdr = countDT.NewRow();
            cdr[0] = num;
            countDT.Rows.Add(cdr);
            ds.Tables.Add(countDT);
            //返回
            return ds;
        }
        #endregion
    }
}
